// SQL格式化和高亮模块

// 从SQL语句中提取表名
export function extractTableNames(sql) {
  const tables = new Set()
  
  // 匹配各种SQL语句模式
  const patterns = [
    // SELECT ... FROM table
    /FROM\s+([a-zA-Z_][a-zA-Z0-9_]*)/gi,
    // INSERT INTO table
    /INSERT\s+INTO\s+([a-zA-Z_][a-zA-Z0-9_]*)/gi,
    // UPDATE table
    /UPDATE\s+([a-zA-Z_][a-zA-Z0-9_]*)/gi,
    // DELETE FROM table
    /DELETE\s+FROM\s+([a-zA-Z_][a-zA-Z0-9_]*)/gi,
    // JOIN table
    /JOIN\s+([a-zA-Z_][a-zA-Z0-9_]*)/gi
  ]
  
  patterns.forEach(pattern => {
    let match
    while ((match = pattern.exec(sql)) !== null) {
      tables.add(match[1])
    }
  })
  
  return Array.from(tables)
}

// 格式化SQL语句，使其更易读
export function formatSQL(sql) {
  // 首先处理基础的空格和换行
  let formatted = sql.trim()
  
  // 定义SQL关键字和分隔符
  const keywords = [
    'SELECT', 'FROM', 'WHERE', 'JOIN', 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'FULL JOIN',
    'ON', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT', 'OFFSET', 'UNION', 'UNION ALL',
    'INSERT', 'INTO', 'VALUES', 'UPDATE', 'SET', 'DELETE', 'TRUNCATE', 'CREATE', 'ALTER',
    'DROP', 'INDEX', 'TABLE', 'DATABASE', 'SCHEMA', 'VIEW', 'PROCEDURE', 'FUNCTION',
    'AND', 'OR', 'NOT', 'IN', 'EXISTS', 'BETWEEN', 'LIKE', 'IS', 'NULL', 'AS',
    'CASE', 'WHEN', 'THEN', 'ELSE', 'END', 'WITH', 'DISTINCT', 'ALL', 'ANY',
    'PRIMARY', 'KEY', 'FOREIGN', 'REFERENCES', 'CONSTRAINT', 'UNIQUE', 'CHECK'
  ]
  
  // 保存字符串字面量
  const stringLiterals = []
  let stringIndex = 0
  
  // 替换所有字符串字面量为占位符
  formatted = formatted.replace(/'([^']*)'/g, (match) => {
    stringLiterals.push(match)
    return `__STRING_${stringIndex++}__`
  })
  
  // 在关键字前后添加换行
  keywords.forEach(keyword => {
    const regex = new RegExp(`\\b${keyword}\\b`, 'gi')
    if (keyword === 'SELECT' || keyword === 'INSERT' || keyword === 'UPDATE' || keyword === 'DELETE') {
      formatted = formatted.replace(regex, `\n${keyword}`)
    } else if (keyword === 'FROM' || keyword === 'WHERE' || keyword === 'SET' || keyword === 'VALUES') {
      formatted = formatted.replace(regex, `\n${keyword}`)
    } else if (keyword === 'AND' || keyword === 'OR') {
      formatted = formatted.replace(regex, `\n  ${keyword}`)
    }
  })
  
  // 处理逗号后的换行
  formatted = formatted.replace(/,\s*/g, ',\n  ')
  
  // 清理多余的空行和空格
  formatted = formatted.replace(/\n\s*\n/g, '\n')
  formatted = formatted.replace(/^\s+/gm, '')
  
  // 添加缩进
  const lines = formatted.split('\n')
  const indentedLines = []
  let indentLevel = 0
  
  lines.forEach(line => {
    const trimmedLine = line.trim()
    if (!trimmedLine) return
    
    // 调整缩进级别
    if (trimmedLine.match(/^(SELECT|INSERT|UPDATE|DELETE|CREATE|ALTER|DROP)/i)) {
      indentLevel = 0
    } else if (trimmedLine.match(/^(FROM|WHERE|SET|VALUES|JOIN|GROUP BY|ORDER BY|HAVING)/i)) {
      indentLevel = 1
    } else if (trimmedLine.match(/^(AND|OR)/i)) {
      indentLevel = 2
    } else if (indentLevel === 0) {
      indentLevel = 1
    }
    
    const indent = '  '.repeat(indentLevel)
    indentedLines.push(indent + trimmedLine)
  })
  
  formatted = indentedLines.join('\n')
  
  // 恢复字符串字面量
  stringLiterals.forEach((literal, index) => {
    formatted = formatted.replace(`__STRING_${index}__`, literal)
  })
  
  return formatted
}

// 为SQL语句添加语法高亮
export function highlightSQL(sql) {
  // SQL关键字
  const keywords = [
    'SELECT', 'FROM', 'WHERE', 'JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'ON',
    'GROUP', 'BY', 'HAVING', 'ORDER', 'LIMIT', 'OFFSET', 'UNION', 'ALL',
    'INSERT', 'INTO', 'VALUES', 'UPDATE', 'SET', 'DELETE', 'TRUNCATE',
    'CREATE', 'ALTER', 'DROP', 'INDEX', 'TABLE', 'DATABASE', 'SCHEMA',
    'CASE', 'WHEN', 'THEN', 'ELSE', 'END', 'IF', 'EXISTS', 'NOT',
    'IN', 'LIKE', 'BETWEEN', 'AND', 'OR', 'IS', 'NULL', 'ASC', 'DESC'
  ]
  
  // 函数名
  const functions = [
    'COUNT', 'SUM', 'AVG', 'MAX', 'MIN', 'NOW', 'CURRENT_TIMESTAMP',
    'UPPER', 'LOWER', 'LENGTH', 'SUBSTRING', 'CONCAT', 'COALESCE'
  ]
  
  let highlighted = sql
  
  // 转义HTML特殊字符
  highlighted = highlighted
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
  
  // 高亮注释（以--开头的行）
  highlighted = highlighted.replace(/^--.*$/gm, '<span class="sql-comment">$&</span>')
  
  // 高亮字符串字面量
  highlighted = highlighted.replace(/'([^']*)'/g, '<span class="sql-string">\'$1\'</span>')
  
  // 高亮数字
  highlighted = highlighted.replace(/\b\d+(\.\d+)?\b/g, '<span class="sql-number">$&</span>')
  
  // 高亮关键字
  keywords.forEach(keyword => {
    const regex = new RegExp(`\\b${keyword}\\b`, 'gi')
    highlighted = highlighted.replace(regex, (match) => {
      // 检查是否已经在span标签内
      const beforeMatch = highlighted.substring(0, highlighted.lastIndexOf(match))
      const openTags = (beforeMatch.match(/<span/g) || []).length
      const closeTags = (beforeMatch.match(/<\/span>/g) || []).length
      
      if (openTags > closeTags) {
        return match // 已经在标签内，不再处理
      }
      
      return `<span class="sql-keyword">${keyword.toUpperCase()}</span>`
    })
  })
  
  // 高亮函数
  functions.forEach(func => {
    const regex = new RegExp(`\\b${func}\\b`, 'gi')
    highlighted = highlighted.replace(regex, (match) => {
      // 检查是否已经在span标签内
      const beforeMatch = highlighted.substring(0, highlighted.lastIndexOf(match))
      const openTags = (beforeMatch.match(/<span/g) || []).length
      const closeTags = (beforeMatch.match(/<\/span>/g) || []).length
      
      if (openTags > closeTags) {
        return match // 已经在标签内，不再处理
      }
      
      return `<span class="sql-function">${func.toUpperCase()}</span>`
    })
  })
  
  return highlighted
}